﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Net;
using System.IO;

namespace SpotsHUB.PlanManager.Samples
{
    class Plan
    {
        public int ID { get; set; }
        public string Driver 
        {
            get
            {
                return String.Format("{0}({1}){2}", DriverFirstName, DriverEmail, DriverLastName, DriverCompany);
            }
        }

        public string DriverEmail { get; set; }
        public string DriverFirstName { get; set; }
        public string DriverLastName { get; set; }
        public string DriverCompany { get; set; }

        public List<Package> Packages = new List<Package>();

        public override bool Equals(object obj)
        {
            return ID == ((Plan)obj).ID;
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }
    }

    class Package
    {
        public int ID { get; set; }
        public int Status { get; set; }
        public string TrackingNumber { get; set; }
        public int ShipmentId { get; set; }
    }

    enum ShippingStatus : int
    {
        /// <summary>
        /// Shipping not required
        /// </summary>
        ShippingNotRequired = 10,
        /// <summary>
        /// Not yet ready
        /// </summary>
        NotYetReady = 15,
        /// <summary>
        /// Not yet shipped
        /// </summary>
        NotYetShipped = 20,
        /// <summary>
        /// Partially shipped
        /// </summary>
        PartiallyShipped = 25,
        /// <summary>
        /// Shipped
        /// </summary>
        Shipped = 30,
        /// <summary>
        /// Delivered
        /// </summary>
        Delivered = 40,
        /// <summary>
        /// ReadyForPickUp
        /// </summary>
        ReadyForPickUp = 50,
        /// <summary>
        /// Delivering
        /// </summary>
        Delivering = 60,
        /// <summary>
        /// ReturningWarehouse
        /// </summary>
        ReturningWarehouse = 70,
        /// <summary>
        /// ReturningWarehouseFailedDelivery
        /// </summary>
        ReturningWarehouseFailedDelivery = 71,
        /// <summary>
        /// InWarehouse
        /// </summary>
        InWarehouse = 80,
        /// <summary>
        /// InWarehouseFailedDelivery
        /// </summary>
        InWarehouseFailedDelivery = 81,
        /// <summary>
        /// FailedPickUp
        /// </summary>
        FailedPickUp = 90,

        /// <summary>
        /// Called delivering
        /// </summary>
        Cancelled = 100,
    }

    class Highlight : IDisposable
    {
        private ConsoleColor old;
        public Highlight(ConsoleColor clr)
        {
            old = Console.ForegroundColor;
            Console.ForegroundColor = clr;
        }

        public void Dispose()
        {
            Console.ForegroundColor = old;
        }
    }

    class Program
    {
        // Provide the following information
        private static string userName = "sitelogin@pv6c15guth";
        private static string password;
        private static string dataSource = "tcp:pv6c15guth.database.windows.net,1433";
        private static string databaseName = "SpotsHub01";

        private static int warehouseId = 0;

        private static StreamWriter file = null;

        private static bool isOutbound = false;

        private static bool genFile = false;

        private static bool refresh = true;

        private static List<Plan> updatedPlans = new List<Plan>();

        static void Main(string[] args)
        {
            file = new StreamWriter(String.Format("PlanManager{0}.log", DateTime.Now.Ticks), true);

            Console.Write("Please input the database you want to connect:");
            databaseName = Console.ReadLine();

            Console.Write("Please input the password:");
            password = ReadPassword();

            Console.Write("Please input the warehouse ID:");
            while (!int.TryParse(Console.ReadLine(), out warehouseId)) ;

            Console.Write("(I)nbound or (O)utbound?");
            char key = Console.ReadKey().KeyChar;
            while (true)
            {
                if (key == 'I' || key == 'i')
                {
                    isOutbound = false;
                    break;
                }
                else if (key == 'O' || key == 'o')
                {
                    Console.BackgroundColor = ConsoleColor.DarkBlue;
                    Console.Clear();
                    isOutbound = true;
                    break;
                }
                key = Console.ReadKey().KeyChar;
            }
            Console.WriteLine();

            Console.Write("(P)rint or (U)pdate plans?");
            key = Console.ReadKey().KeyChar;
            while (true)
            {
                if (key == 'P' || key == 'p')
                {
                    genFile = true;
                    break;
                }
                else if (key == 'U' || key == 'u')
                {
                    genFile = false;
                    break;
                }
                key = Console.ReadKey().KeyChar;
            }
            Console.WriteLine();

            try
            {
                // Create a connection string for the sample database
                SqlConnectionStringBuilder connStringBuilder;
                connStringBuilder = new SqlConnectionStringBuilder();
                connStringBuilder.DataSource = dataSource;
                connStringBuilder.InitialCatalog = databaseName;
                connStringBuilder.Encrypt = true;
                connStringBuilder.TrustServerCertificate = false;
                connStringBuilder.UserID = userName;
                connStringBuilder.Password = password;

                List<Plan> plans = new List<Plan>();
                List<Package> packages = new List<Package>();

                using (SqlConnection conn = new SqlConnection(connStringBuilder.ToString()))
                {
                    using (SqlCommand command = conn.CreateCommand())
                    {
                        while (true)
                        {
                            if (refresh)
                            {
                                conn.Open();
                                Log("Connected to databse {0}.", databaseName);

                                plans.Clear();

                                string cmdText = String.Format("SELECT Id FROM LogisticsWarehouse");
                                command.CommandText = cmdText;
                                command.ExecuteNonQuery();

                                bool pass = false;
                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    // Loop over the results
                                    while (reader.Read())
                                    {
                                        if ((int)reader["Id"] == warehouseId)
                                        {
                                            pass = true;
                                            break;
                                        }
                                    }
                                }

                                if (!pass)
                                {
                                    Log("Warehouse check failed. Exiting...");
                                    conn.Close();
                                    break;
                                }

                                Log("Warehouse check passed.");

                                cmdText = String.Format("SELECT LogisticsPlan.Id, Customer.Email, GenericAttribute.[Key], GenericAttribute.Value FROM Customer INNER JOIN LogisticsPlan ON LogisticsPlan.CourierId = Customer.Id INNER JOIN GenericAttribute ON GenericAttribute.EntityId = Customer.Id WHERE LogisticsPlan.Status = 1 and (GenericAttribute.[Key] = 'FirstName' or GenericAttribute.[Key] = 'LastName' or GenericAttribute.[Key] = 'Company') ORDER BY Customer.Id");
                                command.CommandText = cmdText;
                                command.ExecuteNonQuery();

                                using (SqlDataReader reader = command.ExecuteReader())
                                {
                                    // Loop over the results
                                    while (reader.Read())
                                    {
                                        var planId = (int)reader["Id"];
                                        var driverEmail = reader["Email"].ToString().Trim();

                                        Plan plan = plans.SingleOrDefault(x => x.ID == planId);
                                        if (plan == null)
                                        {
                                            plan = new Plan() { ID = planId, DriverEmail = driverEmail };
                                            plans.Add(plan);
                                        }

                                        if (reader["Key"].ToString() == "FirstName")
                                        {
                                            plan.DriverFirstName = reader["Value"].ToString();
                                        }

                                        if (reader["Key"].ToString() == "LastName")
                                        {
                                            plan.DriverLastName = reader["Value"].ToString();
                                        }

                                        if (reader["Key"].ToString() == "Company")
                                        {
                                            plan.DriverCompany = reader["Value"].ToString();
                                        }
                                    }
                                }

                                conn.Close();

                                #region Populate packages

                                conn.Open();

                                foreach (var p in plans)
                                {
                                    p.Packages.Clear();

                                    cmdText = String.Format("SELECT LogisticsPackage.Id, LogisticsPackage.Status, Shipment.TrackingNumber, Shipment.Id AS ShipmentId FROM LogisticsPackage INNER JOIN LogisticsLocation ON LogisticsPackage.Id = LogisticsLocation.PackageId INNER JOIN Shipment ON LogisticsPackage.ShipmentId = Shipment.Id WHERE LogisticsLocation.PlanId = {0}", p.ID);
                                    command.CommandText = cmdText;
                                    command.ExecuteNonQuery();

                                    using (SqlDataReader reader = command.ExecuteReader())
                                    {
                                        // Loop over the results
                                        while (reader.Read())
                                        {
                                            int id = (int)reader["Id"];
                                            if (p.Packages.All(x => x.ID != id))
                                            {
                                                p.Packages.Add(new Package() { ID = id, Status = (int)reader["Status"], ShipmentId = (int)reader["ShipmentId"], TrackingNumber = (string)reader["TrackingNumber"] });
                                            }
                                        }
                                    }
                                }

                                conn.Close();

                                #endregion

                                refresh = false;
                            }

                            plans.ForEach(x => Log("ID: {0}, {1},Package count {2}", x.ID, x.Driver, x.Packages.Count));

                            if (genFile)
                            {
                                #region Report
                                List<Plan> plansToPrint = new List<Plan>();

                                while (true)
                                {
                                    Console.WriteLine("Input the plan which you want to print, 0 to print all plans, 1 to query packages, -1 to quit:");
                                    int id;

                                    var inStr = Console.ReadLine();

                                    while (!int.TryParse(inStr, out id) || (id != 1 && id != 0 && id != -1))
                                    {
                                        try
                                        {
                                            var pIds = inStr
                                                .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
                                                .Select(x => int.Parse(x))
                                                .ToList();
                                            plansToPrint = plans.Where(x => pIds.Contains(x.ID)).ToList();

                                            if (plansToPrint.Count > 0 && pIds.All(y => plans.Any(x => x.ID == y)))
                                            {
                                                break;
                                            }

                                            Console.Write("Please input correct plan IDs:");
                                            inStr = Console.ReadLine();
                                        }
                                        catch (Exception)
                                        {
                                        }
                                    }
                                    if (id < 0)
                                    {
                                        break;
                                    }

                                    if (id == 1)
                                    {
                                        Console.WriteLine("Input the package Id you want to query:");
                                        int pkgId;
                                        var allPackages = plans.SelectMany(x => x.Packages).ToList();
                                        while (int.TryParse(Console.ReadLine(), out pkgId) && pkgId > 0)
                                        {
                                            var pkg = allPackages.SingleOrDefault(x => x.ID == pkgId);
                                            if (pkg != null)
                                            {

                                                Log("ID: {0}, Status {1}, Shipment Id {2}, Tracking# {3}", pkg.ID, (ShippingStatus)pkg.Status, pkg.ShipmentId, pkg.TrackingNumber);
                                                System.Diagnostics.Process.Start(String.Format("https://www.spotshub.com/Admin/Order/PrintPackageWaybillAsPdf?shipmentId={0}", pkg.ShipmentId));
                                            }
                                            else
                                            {
                                                conn.Open();

                                                var cmdText = String.Format("SELECT LogisticsPackage.Id, LogisticsPackage.Status, Shipment.TrackingNumber, Shipment.Id AS ShipmentId FROM LogisticsPackage INNER JOIN LogisticsLocation ON LogisticsPackage.Id = LogisticsLocation.PackageId INNER JOIN Shipment ON LogisticsPackage.ShipmentId = Shipment.Id WHERE LogisticsPackage.Id = {0}", pkgId);
                                                command.CommandText = cmdText;
                                                command.ExecuteNonQuery();

                                                using (SqlDataReader reader = command.ExecuteReader())
                                                {
                                                    // Loop over the results
                                                    while (reader.Read())
                                                    {
                                                        System.Diagnostics.Process.Start(String.Format("https://www.spotshub.com/Admin/Order/PrintPackageWaybillAsPdf?shipmentId={0}", (int)reader["ShipmentId"]));
                                                    }
                                                }

                                                conn.Close();
                                            }
                                            Console.WriteLine("Input the package Id you want to query:");
                                        }
                                    }
                                    else
                                    {
                                        var reportfn = String.Format("Report{0}.txt", DateTime.Now.Ticks);
                                        var report = new StreamWriter(reportfn, true);

                                        if (plansToPrint != null && plansToPrint.Count > 0)
                                        {
                                        }
                                        else if (id == 0)
                                        {
                                            plansToPrint = plans;
                                        }

                                        plansToPrint.ForEach(x => printPlan(report, x));

                                        report.Close();

                                        System.Diagnostics.Process.Start(reportfn);
                                    }
                                }

                                Console.WriteLine("Do you want to (R)efresh or (E)xit?");
                                char k = Console.ReadKey().KeyChar;
                                if (k == 'r' || k == 'R')
                                {
                                    refresh = true;
                                }
                                else if (k == 'e' || k == 'E')
                                {
                                    return;
                                }

                                #endregion
                            }
                            else
                            {
                                #region Update plan
                                Console.Write("Input the plan ID to work on, 0 to exit, -1 to refresh:");
                                int planId;
                                while (!int.TryParse(Console.ReadLine(), out planId) || (planId != 0 && planId != -1 && !plans.Any(x => x.ID == planId)))
                                {
                                    Console.Write("Please input a correct plan ID:");
                                }

                                if (planId == -1)
                                {
                                    refresh = true;
                                    continue;
                                }

                                if (planId == 0)
                                {
                                    Log("Exiting on demond.");
                                    conn.Close();
                                    break;
                                }

                                packages = plans.Single(x => x.ID == planId).Packages.ToList();

                                Console.WriteLine("Totally {0} packages in the plan {1}. Please make sure they are correct.", packages.Count, planId);
                                packages.ForEach(x => Log("ID: {0}, Status {1}, Shipment Id {2}, Tracking# {3}", x.ID, (ShippingStatus)x.Status, x.ShipmentId, x.TrackingNumber));

                                Console.WriteLine("========================================");

                                List<Package> updatingPkgs, errorPkgs = null;
                                if (!isOutbound)
                                {
                                    updatingPkgs = packages.Where((x => x.Status == 70 || x.Status == 71)).ToList();
                                    errorPkgs = packages.Where(x => x.Status != 70 && x.Status != 71 && x.Status != 40).ToList();
                                }
                                else
                                {
                                    updatingPkgs = packages.Where((x => x.Status == 80 || x.Status == 81)).ToList();
                                    errorPkgs = packages.Where(x => x.Status != 80 && x.Status != 81 && x.Status != 50).ToList();
                                }

                                if (updatingPkgs.Count() == 0)
                                {
                                    Log("No packages to update.");
                                    continue;
                                }

                                if (errorPkgs != null && errorPkgs.Count() != 0)
                                {
                                    using (new Highlight(ConsoleColor.Red))
                                    {

                                        errorPkgs.ForEach(x => Console.WriteLine("ID: {0}, Status {1}", x.ID, (ShippingStatus)x.Status));
                                        Console.WriteLine("The above package status is unexpected. Do you want to continue? input (YES)");
                                    }
                                    if (Console.ReadLine() != "YES")
                                    {
                                        continue;
                                    }
                                }

                                using (new Highlight(ConsoleColor.Green))
                                {
                                    if (!isOutbound)
                                    {
                                        Console.WriteLine("Please verify the following packages have been in the warehouse.");
                                    }
                                    else
                                    {
                                        Console.WriteLine("Please verify the following packages have been loaded correctly.");
                                    }

                                    updatingPkgs.ForEach(x => Console.WriteLine("ID: {0}, Status {1}", x.ID, (ShippingStatus)x.Status));
                                    Console.WriteLine("========================================");
                                    Console.WriteLine("{0} packages in total.", updatingPkgs.Count);
                                }
                                if (!isOutbound)
                                {
                                    Console.WriteLine("Type 'YES' to confirm changing packages to 'In HUB', or the operation will be cancelled.");
                                }
                                else
                                {
                                    Console.WriteLine("Type 'YES' to confirm changing packages to 'In Transit', or the operation will be cancelled.");
                                }

                                if (Console.ReadLine() != "YES")
                                {
                                    continue;
                                }

                                updatedPlans.Add(plans.Single(x => x.ID == planId));

                                conn.Open();

                                // Start a local transaction.
                                SqlTransaction sqlTran = conn.BeginTransaction();
                                command.Transaction = sqlTran;

                                try
                                {
                                    if (!isOutbound)
                                    {
                                        foreach (var pkg in updatingPkgs)
                                        {
                                            if (pkg.Status != 70 && pkg.Status != 71)
                                            {
                                                throw new Exception(String.Format("Package ({0}) status is incorrect!", pkg.ID));
                                            }

                                            int status = pkg.Status == 70 ? 80 : 81;

                                            command.CommandText =
                                                String.Format("UPDATE LogisticsPackage SET Status = {0}, WarehouseId = {1} WHERE Id = {2}", status, warehouseId, pkg.ID);
                                            command.ExecuteNonQuery();

                                            command.CommandText =
                                                String.Format("UPDATE LogisticsLocation SET [PlanId] = NULL, [Rank] = NULL WHERE PackageId = {0}", pkg.ID);
                                            command.ExecuteNonQuery();
                                        }

                                        command.CommandText =
                                            String.Format("UPDATE LogisticsPlan SET [Status] = 2 WHERE Id = {0}", planId);
                                        command.ExecuteNonQuery();
                                    }
                                    else
                                    {
                                        foreach (var pkg in updatingPkgs)
                                        {
                                            if (pkg.Status != 80 && pkg.Status != 81)
                                            {
                                                throw new Exception(String.Format("Package ({0}) status is incorrect!", pkg.ID));
                                            }

                                            int status = 60;

                                            command.CommandText =
                                                String.Format("UPDATE LogisticsPackage SET Status = {0} WHERE Id = {1}", status, pkg.ID);
                                            command.ExecuteNonQuery();
                                        }
                                    }

                                    // Commit the transaction.
                                    sqlTran.Commit();
                                    using (new Highlight(ConsoleColor.Green))
                                    {
                                        Log("All packages have been updated successfully! Please check the rest packages in the plan.");
                                    }

                                    if (updatedPlans.Count > 0)
                                    {

                                        using (new Highlight(ConsoleColor.DarkRed))
                                        {
                                            Console.WriteLine("The following plans have been updated.");
                                            updatedPlans.ForEach(x => Log("ID: {0}, Driver Email {1}, Package count {2}", x.ID, x.Driver, x.Packages.Count));
                                        }
                                    }
                                }
                                catch (Exception ex)
                                {
                                    using (new Highlight(ConsoleColor.Red))
                                    {
                                        // Handle the exception if the transaction fails to commit.
                                        Log("Trans failed: {0}", ex);
                                    }
                                    try
                                    {
                                        // Attempt to roll back the transaction.
                                        sqlTran.Rollback();
                                    }
                                    catch (Exception exRollback)
                                    {
                                        // Throws an InvalidOperationException if the connection  
                                        // is closed or the transaction has already been rolled  
                                        // back on the server.
                                        Log(exRollback.Message);
                                    }
                                }

                                conn.Close();
                                #endregion

                                Console.WriteLine("Do you want to refresh? (Y/N)");
                                char k = Console.ReadKey().KeyChar;
                                if (k == 'y' || k == 'Y')
                                {
                                    refresh = true;
                                }
                                else
                                {
                                    plans.Remove(plans.SingleOrDefault(x => x.ID == planId));
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (new Highlight(ConsoleColor.Red))
                {
                    Log("Fatal error: {0}", ex);
                    Log("Your IP address is {0}", GetExternalIp());
                }
            }
            finally
            {
                if (file != null)
                {
                    file.Close();
                    file = null;
                }
            }
        }

        private static void printPlan(StreamWriter report, Plan p)
        {
            if (isOutbound)
            {
                if (p.Packages.Any(x => x.Status == 80 || x.Status == 81))
                {
                    report.WriteLine();
                    report.WriteLine();
                    report.WriteLine(">>>>>>> {0}", p.Driver);
                    report.Write("Plan {0}--{1}--{2}\t{3}\t", p.Driver, p.ID, DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
                    var pkgs = p.Packages
                        .Where(x => x.Status == 80 || x.Status == 81)
                        .ToList();
                    report.WriteLine("Packages#: {0}", pkgs.Count);
                    report.WriteLine("\tPkg#\tShip#\tTracking#\t\tStatus");
                    pkgs.ForEach(x => report.WriteLine("\t{0}|\t{1}|\t{2}|\t{3}", x.ID, x.ShipmentId, x.TrackingNumber, (ShippingStatus)x.Status));
                    report.WriteLine("<<<<<<< {0}", p.Driver);
                    report.WriteLine();
                    report.WriteLine();
                }
            }
            else
            {
                if (p.Packages.Any(x => x.Status == 70 || x.Status == 71))
                {
                    report.WriteLine();
                    report.WriteLine();
                    report.WriteLine(">>>>>>> {0}", p.Driver);
                    report.Write("Plan {0}--{1}--{2}\t{3}\t", p.Driver, p.ID, DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
                    var pkgs = p.Packages
                        .Where(x => x.Status == 70 || x.Status == 71 || x.Status == 50)
                        .ToList();
                    report.WriteLine("Packages: {0}", pkgs.Count);
                    report.WriteLine("\tPkg#\tShip#\tTracking#\t\tStatus");
                    pkgs.ForEach(x => report.WriteLine("\t{0}|\t{1}|\t{2}|\t{3}", x.ID, x.ShipmentId, x.TrackingNumber, (ShippingStatus)x.Status));
                    report.WriteLine("<<<<<<< {0}", p.Driver);
                    report.WriteLine();
                    report.WriteLine();
                }
            }
        }

        static string GetExternalIp()
        {
            string url = "http://checkip.dyndns.org/";
            WebClient webClient = new WebClient();
            Stream data = webClient.OpenRead(url);
            StreamReader reader = new StreamReader(data);
            string s = reader.ReadToEnd();
            s = s.Split(':')[1];
            s = s.Substring(1);
            string res = s.Split('<')[0];
            data.Close();
            reader.Close();

            return res;
        }

        static void Log(string format, params object[] args)
        {
            Console.WriteLine(format, args);

            if (file != null)
            {
                file.Write("{0} {1}:", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString());
                file.WriteLine(format, args);
                file.Flush();
            }
        }

        static string ReadPassword()
        {
            string password = "";
            ConsoleKeyInfo info = Console.ReadKey(true);
            while (info.Key != ConsoleKey.Enter)
            {
                if (info.Key != ConsoleKey.Backspace)
                {
                    Console.Write("*");
                    password += info.KeyChar;
                }
                else if (info.Key == ConsoleKey.Backspace)
                {
                    if (!string.IsNullOrEmpty(password))
                    {
                        // remove one character from the list of password characters        
                        password = password.Substring(0, password.Length - 1);
                        // get the location of the cursor                    
                        int pos = Console.CursorLeft;
                        // move the cursor to the left by one character     
                        Console.SetCursorPosition(pos - 1, Console.CursorTop);
                        // replace it with space          
                        Console.Write(" ");
                        // move the cursor to the left by one character again           
                        Console.SetCursorPosition(pos - 1, Console.CursorTop);
                    }
                }
                info = Console.ReadKey(true);
            }
            // add a new line because user pressed enter at the end of their password  
            Console.WriteLine();
            return password;
        }
    }
}